package net.huashitong.supervision.task.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.supervision.task.entity.CopeTo;
import net.huashitong.supervision.task.entity.TaskMain;
import net.huashitong.supervision.task.entity.TaskUnit;
import net.huashitong.supervision.task.filter.TaskMainFilter;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: ling
 * Date: 2019/8/19
 * Time: 18:07
 * To change this template use File | Settings | File Templates.
 * Description:
 */
@Repository
public class CopeToDao extends StringDao<CopeTo> {
    /**
     * 查询待办的抄送
     * @param filter
     * @return
     */
    public List<TaskMain> queryUnDone(TaskMainFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select DISTINCT t1.sys_id sysId,t1.main_name mainName,t1.category_id categoryId ,t1.time_limit timeLimit,t1.type_explain typeExplain ,t1.overdue_explain overdueExplain ,t1.status as status from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }

        sql.append(" and t2.status in ('0','2')");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        // 设置查询总条数,用于分页
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            int resultSize = this.countUnDone(filter);
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }
            SQLQuery query = getSession().createSQLQuery(sql.toString());

            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskMain.class));
            List<TaskMain> list = query.list();
            return list;
        } else {
            sql.append(" order by t1.sys_id desc ");
            SQLQuery query = getSession().createSQLQuery(sql.toString());
            List<TaskMain> list = query.list();
            return list;
        }
    }
    private int countUnDone(TaskMainFilter filter){
        StringBuilder sql = new StringBuilder();
        sql.append("select count(distinct t1.sys_id) from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null");

        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }

        sql.append(" and t2.status in ('0','2')");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }

    public List<CopeTo> queryByleaderIdAndTaskMainId(String userId, String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");
        query.append(" and leaderId =:leaderId").setParam("leaderId",userId );
        query.append(" and taskMainId =:taskMainId").setParam("taskMainId",taskMainId );
        return query.listResult();
    }

    public List<CopeTo> getByTaskMainId(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del is null");
        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        return query.listResult();
    }
    public List<CopeTo> getByTaskMainIdDel(String taskMainId) {
        GenericQuery query = createQuery("obj");
        query.append(" and del =:del").setParam("del", "0");
        query.append(" and taskMainId =:taskMainId").setParam("taskMainId", taskMainId);
        return query.listResult();
    }

    /**
     * 在办的抄送
     * @param filter
     * @return
     */
    public List<TaskMain> queryNowDone(TaskMainFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select DISTINCT t1.sys_id sysId,t1.main_name mainName,t1.category_id categoryId ,t1.time_limit timeLimit,t1.type_explain typeExplain ,t1.overdue_explain overdueExplain ,t1.status as status from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }

        sql.append(" and t2.status !='3'");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        // 设置查询总条数,用于分页
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            int resultSize = this.countNowDone(filter);
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }
            SQLQuery query = getSession().createSQLQuery(sql.toString());

            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskMain.class));
            List<TaskMain> list = query.list();
            return list;
        } else {
            sql.append(" order by t1.sys_id desc ");

            SQLQuery query = getSession().createSQLQuery(sql.toString());
            List<TaskMain> list = query.list();
            return list;
        }
    }
    private int countNowDone(TaskMainFilter filter){
        StringBuilder sql = new StringBuilder();
        sql.append("select count(distinct t1.sys_id) from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null ");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }
        sql.append(" and t2.status ='1' ");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }

    /**
     * 已办
     * @param filter
     * @return
     */

    public List<TaskMain> queryDone(TaskMainFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select DISTINCT t1.sys_id sysId,t1.main_name mainName,t1.category_id categoryId ,t1.time_limit timeLimit,t1.type_explain typeExplain ,t1.overdue_explain overdueExplain ,t1.status as status from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null ");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
       /* if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }*/
        sql.append(" and t2.status ='3'");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        // 设置查询总条数,用于分页
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            int resultSize = this.countDone(filter);
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }
            SQLQuery query = getSession().createSQLQuery(sql.toString());

            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskMain.class));
            List<TaskMain> list = query.list();
            return list;
        } else {
            sql.append(" order by t1.sys_id desc ");

            SQLQuery query = getSession().createSQLQuery(sql.toString());
            List<TaskMain> list = query.list();
            return list;
        }
    }
    private int countDone(TaskMainFilter filter){
        StringBuilder sql = new StringBuilder();
        sql.append("select count(distinct t1.sys_id) from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null ");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }

        sql.append(" and t2.status ='3' ");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }
    public List<TaskMain> queryAllDone(TaskMainFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select DISTINCT t1.sys_id sysId,t1.main_name mainName,t1.category_id categoryId ,t1.time_limit timeLimit,t1.type_explain typeExplain ,t1.overdue_explain overdueExplain ,t1.status as status, " +
                " t1.file_name fileName, t1.file file, t1.gjfile gJFile,t1.gjfile_name gJFileName,t1.leader_follow leaderFollow  from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null ");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }

//        sql.append(" and t2.status ='3')");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        // 设置查询总条数,用于分页
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            int resultSize = this.countAllDone(filter);
            filter.getPaging().setRecords(resultSize);
            //设置排序
            if (ObjectUtils.isNotBlank(filter.getPaging())) {
                if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                    filter.getPaging().setSortCol("t1.sys_id");
                    filter.getPaging().setSortVal("desc");
                }
                sql.append(" order by " + filter.getPaging().getSortCol() + " " + filter.getPaging().getSortVal());
            }
            SQLQuery query = getSession().createSQLQuery(sql.toString());

            query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
            query.setMaxResults(filter.getPaging().getPageSize());
            query.setResultTransformer(Transformers.aliasToBean(TaskMain.class));
            List<TaskMain> list = query.list();
            return list;
        } else {
            sql.append(" order by t1.sys_id desc ");
            SQLQuery query = getSession().createSQLQuery(sql.toString());
            query.setResultTransformer(Transformers.aliasToBean(TaskMain.class));
            List<TaskMain> list = query.list();
            return list;
        }
    }
    private int countAllDone(TaskMainFilter filter){
        StringBuilder sql = new StringBuilder();
        sql.append("select count(distinct t1.sys_id) from t_task_main t1 ,t_cope_to t2 where t1.sys_id = t2.task_main_id ");
        sql.append(" and t1.del is null");
        if (StringUtils.isNotBlank(filter.getMainName())) {
            sql.append(" and t1.main_name like '%" + filter.getMainName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getStatus())) {
            sql.append(" and t1.status ='" + filter.getStatus() + "'");
        }

//        sql.append(" and t2.status ='3' ");
        sql.append(" and t2.leader_id ='" + filter.getCopeTo() + "'");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }
}

